import sys

import pandas as pd
import pymysql

# 1. 读取Excel文件
data = pd.read_excel("各汽车厂商各车型分月度销售量排名及售价数据.xlsx")
df = data.iloc[:,:-1].dropna()
# df.to_excel('汽车销售数据-已清理.xlsx', index=False)
# sys.exit()
# 2. 建立数据库连接
conn = pymysql.connect(
    host='mysql5.sqlpub.com',
    port=3310,
    user='root133',
    password='xSipNzqyvtBioMdU',
    database='stat133',
    charset='utf8'
)



# 3. 准备批量插入
cursor = conn.cursor()

# 4. 构建SQL语句
sql = """
INSERT INTO cars
(year, month, ranking, model, manufacturer, sales_volume, price_range)
VALUES (%s, %s, %s, %s, %s, %s, %s)
"""

# 5. 执行批量插入
try:
    # 将DataFrame转换为元组列表
    data = [tuple(x) for x in df.values]
    cursor.executemany(sql, data)
    conn.commit()
    print(f"成功导入 {len(df)} 条数据")
except Exception as e:
    conn.rollback()
    print("导入失败:", e)
finally:
    cursor.close()
    conn.close()